package ranveterinaria.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import ranveterinaria.excepcion.DAOExcepcion;
import ranveterinaria.modelo.DoctorModelo;
import ranveterinaria.util.ConexionBD;


public class DoctorDAO extends BaseDAO {

	
	public void insertar(DoctorModelo vo) throws DAOExcepcion {
		String query = "insert into doctor(nombre_doctor,ape_pat_doctor,ape_mat_doctor,email_doctor,tlf_doctor,bol_admin_doctor) values (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getNombreDoctor() );
			stmt.setString(2, vo.getApePatDoctor() );
			stmt.setString(3, vo.getApeMatDoctor() );
			stmt.setString(4, vo.getEmailDoctor() );
			stmt.setString(5, vo.getTlfDoctor() );
			stmt.setBoolean(6, vo.getBolAdminDoctor() );
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}			

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}		
	}
	
	public DoctorModelo obtener(String mail) throws DAOExcepcion {
		DoctorModelo c = null;
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
                con = ConexionBD.obtenerConexion();
                String query = "select codigo_doctor,nombre_doctor,ape_pat_doctor,ape_mat_doctor,email_doctor,tlf_doctor,bol_admin_doctor from doctor where email_doctor = ?";
                stmt = con.prepareStatement(query);
                stmt.setString(1, mail);
                rs = stmt.executeQuery();
                while (rs.next()) {
                	DoctorModelo vo = new DoctorModelo();
                    vo.setCodigoDoctor(rs.getInt("codigo_doctor"));
                    vo.setNombreDoctor(rs.getString("nombre_doctor"));
                    vo.setApePatDoctor(rs.getString("ape_pat_doctor"));
                    vo.setApeMatDoctor(rs.getString("ape_mat_doctor"));
                    vo.setEmailDoctor(rs.getString("email_doctor"));
                    vo.setTlfDoctor(rs.getString("tlf_doctor"));
                    vo.setBolAdminDoctor(rs.getBoolean("bol_admin_doctor"));                                                
                    return vo;
                }

        } catch (SQLException e) {
                System.err.println(e.getMessage());
                throw new DAOExcepcion(e.getMessage());
        } finally {
                this.cerrarResultSet(rs);
                this.cerrarStatement(stmt);
                this.cerrarConexion(con);
        }
        return c;
	}
	
	public Collection<DoctorModelo> listar() throws DAOExcepcion {
        Collection<DoctorModelo> c = new ArrayList<DoctorModelo>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
                con = ConexionBD.obtenerConexion();
                String query = "select codigo_doctor,nombre_doctor,ape_pat_doctor,ape_mat_doctor,email_doctor,tlf_doctor,bol_admin_doctor from doctor order by codigo_doctor desc";
                stmt = con.prepareStatement(query);
                rs = stmt.executeQuery();
                while (rs.next()) {
                	DoctorModelo vo = new DoctorModelo();
                        vo.setCodigoDoctor(rs.getInt("codigo_doctor"));
                        vo.setNombreDoctor(rs.getString("nombre_doctor"));
                        vo.setApePatDoctor(rs.getString("ape_pat_doctor"));
                        vo.setApeMatDoctor(rs.getString("ape_mat_doctor"));
                        vo.setEmailDoctor(rs.getString("email_doctor"));
                        vo.setTlfDoctor(rs.getString("tlf_doctor"));
                        vo.setBolAdminDoctor(rs.getBoolean("bol_admin_doctor"));                                                
                        c.add(vo);
                }

        } catch (SQLException e) {
                System.err.println(e.getMessage());
                throw new DAOExcepcion(e.getMessage());
        } finally {
                this.cerrarResultSet(rs);
                this.cerrarStatement(stmt);
                this.cerrarConexion(con);
        }
        return c;
}

	  public DoctorModelo actualizar(DoctorModelo vo) throws DAOExcepcion {
          String query = "update doctor set nombre_doctor=? ,ape_pat_doctor=? , ape_mat_doctor=? , email_doctor=? , tlf_doctor=? , bol_admin_doctor = ?  where codigo_doctor=?";
          Connection con = null;
          PreparedStatement stmt = null;
          try {
                  con = ConexionBD.obtenerConexion();
                  stmt = con.prepareStatement(query);
                  stmt.setString(1, vo.getNombreDoctor()  );
                  stmt.setString(2, vo.getApePatDoctor()  );
                  stmt.setString(3, vo.getApeMatDoctor() );
                  stmt.setString(4, vo.getEmailDoctor()  );
                  stmt.setString(5, vo.getTlfDoctor()  );
                  stmt.setBoolean(6, vo.getBolAdminDoctor()  );
                  stmt.setInt(7, vo.getCodigoDoctor() );
                  int i = stmt.executeUpdate();
                  if (i != 1) {
                          throw new SQLException("No se pudo actualizar");
                  }
          } catch (SQLException e) {
                  System.err.println(e.getMessage());
                  throw new DAOExcepcion(e.getMessage());
          } finally {
                  this.cerrarStatement(stmt);
                  this.cerrarConexion(con);
          }
          return vo;
  }
	  
	  public void eliminar(int idDoctor) throws DAOExcepcion {
          String query = "delete from doctor WHERE codigo_doctor=?";
          Connection con = null;
          PreparedStatement stmt = null;
          try {
                  con = ConexionBD.obtenerConexion();
                  stmt = con.prepareStatement(query);
                  stmt.setInt(1, idDoctor);
                  int i = stmt.executeUpdate();
                  if (i != 1) {
                          throw new SQLException("No se pudo eliminar");
                  }
          } catch (SQLException e) {
                  System.err.println(e.getMessage());
                  throw new DAOExcepcion(e.getMessage());
          } finally {
                  this.cerrarStatement(stmt);
                  this.cerrarConexion(con);
          }
  }
	  
	
}
